﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_Order
    {
        public const string usp_SelectOrdersAll       = "usp_SelectOrdersAll";
        public const string usp_SelectOrder           = "usp_SelectOrder";
        public const string usp_SelectOrdersType      = "usp_SelectOrdersType";
        public const string usp_SelectOrdersDynamic   = "usp_SelectOrdersDynamic";
        public const string usp_InsertUpdateOrder     = "usp_InsertUpdateOrder";
        public const string usp_SelectOrdersMaxID     = "usp_SelectOrdersMaxID";
        public const string usp_DeleteOrder           = "usp_DeleteOrder";
        public const string usp_SelectOrdersCode      = "usp_SelectOrdersCode";
        public const string usp_SelectOrderList       = "usp_SelectOrderList";
        public const string usp_SelectOrder_OrderCode = "usp_SelectOrder_OrderCode";
        
        public string Get_Order_Code()
        {
            DataTable dt = new DataTable();
            string strReturn = "";
            SqlCommand sqlCommand = new SqlCommand(usp_SelectOrdersCode, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                Utils.sqlConnection.Open();
                strReturn=sqlCommand.ExecuteScalar().ToString();
                Utils.sqlConnection.Close();
            }
            catch
            {
                strReturn= "00000000";
            }
            return strReturn;
        }

        public DataTable Get_Order_All()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectOrdersAll, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                return dt;
            }
            return dt;
        }

        public DataTable Get_OrderList(string pStatus)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectOrderList, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@STATUS";
            sqlParameter[0].Value = pStatus;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Order_OrderCode(string pOrderCode)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectOrder_OrderCode, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ORDER_CODE";
            sqlParameter[0].Value = pOrderCode;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Order(Int64 pID)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectOrder, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int32;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Order_Dynamic(string pCondition)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectOrdersDynamic, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@WhereCondition";
            sqlParameter[0].Value = pCondition;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public bool InsertUpdate_Order(Order pOrder)
        {
            SqlParameter[] sqlParameter = new SqlParameter[18];
            SqlCommand sqlCommand = new SqlCommand(usp_InsertUpdateOrder, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pOrder.ID;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].DbType = DbType.Int64;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@ID_PRODUCT";
            sqlParameter[1].Value = pOrder.ID_PRODUCT;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].DbType = DbType.Int64;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@QUANTITY";
            sqlParameter[2].Value = pOrder.QUANTITY;

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].DbType = DbType.Int64;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@PRICE";
            sqlParameter[3].Value = pOrder.PRICE;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].DbType = DbType.Int64;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@VALUE";
            sqlParameter[4].Value = pOrder.VALUE;

            sqlParameter[5] = new SqlParameter();
            sqlParameter[5].DbType = DbType.Int64;
            sqlParameter[5].Direction = ParameterDirection.Input;
            sqlParameter[5].ParameterName = "@TAX";
            sqlParameter[5].Value = pOrder.TAX;

            sqlParameter[6] = new SqlParameter();
            sqlParameter[6].DbType = DbType.Int64;
            sqlParameter[6].Direction = ParameterDirection.Input;
            sqlParameter[6].ParameterName = "@TOTAL";
            sqlParameter[6].Value = pOrder.TOTAL;

            sqlParameter[7] = new SqlParameter();
            sqlParameter[7].DbType = DbType.String;
            sqlParameter[7].Direction = ParameterDirection.Input;
            sqlParameter[7].ParameterName = "@FULLNAME";
            sqlParameter[7].Value = pOrder.FULLNAME;

            sqlParameter[8] = new SqlParameter();
            sqlParameter[8].DbType = DbType.String;
            sqlParameter[8].Direction = ParameterDirection.Input;
            sqlParameter[8].ParameterName = "@PHONE";
            sqlParameter[8].Value = pOrder.PHONE;

            sqlParameter[9] = new SqlParameter();
            sqlParameter[9].DbType = DbType.String;
            sqlParameter[9].Direction = ParameterDirection.Input;
            sqlParameter[9].ParameterName = "@EMAIL";
            sqlParameter[9].Value = pOrder.EMAIL;

            sqlParameter[10] = new SqlParameter();
            sqlParameter[10].DbType = DbType.DateTime;
            sqlParameter[10].Direction = ParameterDirection.Input;
            sqlParameter[10].ParameterName = "@ORDER_DATE";
            sqlParameter[10].Value = pOrder.ORDER_DATE;

            sqlParameter[11] = new SqlParameter();
            sqlParameter[11].DbType = DbType.DateTime;
            sqlParameter[11].Direction = ParameterDirection.Input;
            sqlParameter[11].ParameterName = "@RECEIVE_DATE";
            sqlParameter[11].Value = pOrder.RECEIVE_DATE;

            sqlParameter[12] = new SqlParameter();
            sqlParameter[12].DbType = DbType.String;
            sqlParameter[12].Direction = ParameterDirection.Input;
            sqlParameter[12].ParameterName = "@ADDRESS";
            sqlParameter[12].Value = pOrder.ADDRESS;

            sqlParameter[13] = new SqlParameter();
            sqlParameter[13].DbType = DbType.String;
            sqlParameter[13].Direction = ParameterDirection.Input;
            sqlParameter[13].ParameterName = "@DESCRIPTION";
            sqlParameter[13].Value = pOrder.DESCRIPTION;

            sqlParameter[14] = new SqlParameter();
            sqlParameter[14].DbType = DbType.String;
            sqlParameter[14].Direction = ParameterDirection.Input;
            sqlParameter[14].ParameterName = "@TYPE_PAY";
            sqlParameter[14].Value = pOrder.TYPE_PAY;

            sqlParameter[15] = new SqlParameter();
            sqlParameter[15].DbType = DbType.String;
            sqlParameter[15].Direction = ParameterDirection.Input;
            sqlParameter[15].IsNullable = true;
            sqlParameter[15].ParameterName = "@STATUS";
            sqlParameter[15].Value = pOrder.STATUS;

            sqlParameter[16] = new SqlParameter();
            sqlParameter[16].DbType = DbType.String;
            sqlParameter[16].Direction = ParameterDirection.Input;
            sqlParameter[16].ParameterName = "@CITY";
            sqlParameter[16].Value = pOrder.CITY;

            sqlParameter[17] = new SqlParameter();
            sqlParameter[17].DbType = DbType.String;
            sqlParameter[17].Direction = ParameterDirection.Input;
            sqlParameter[17].ParameterName = "@ORDER_CODE";
            sqlParameter[17].Value = pOrder.ORDER_CODE;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public bool DeleteOrder(Int64 pID)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteOrder, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }
    }
}
